// .........................................................................
// Title: firm_outcomes.do
//
// Prepares data on firm-level outcomes for analysis
// .........................................................................

* ---------------------------------------
* Firm IDs using aggregation
* ---------------------------------------

* mapping from cusip to ultimate parent firm id
use "$raw/cmns/gcap_security_master_cusip", clear
keep cusip
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m)
drop _merge *source*

replace cusip6_up_bg = issuer_number if missing(cusip6_up_bg)
replace country_bg = cgs_domicile if missing(country_bg)
replace issuer_name_up = issuer_name if missing(issuer_name_up)

drop issuer_number cgs_domicile issuer_name
rename cusip6_up_bg firm_id
rename country_bg firm_domicile
rename issuer_name_up firm_name
keep cusip firm_id firm_domicile firm_name

mmerge firm_id using "$tmp/industry_master_naics", unmatched(m) umatch(cusip6)
drop _merge
gsort cusip
save "$tmp/cusip_to_up_firm_id", replace

* cusip8 version
use "$tmp/cusip_to_up_firm_id", clear
replace cusip = substr(cusip, 1, 8)
gen len_cusip = strlen(cusip)
assert len_cusip == 8
drop len_cusip
save "$tmp/cusip8_to_up_firm_id", replace

* ---------------------------------------
* New issuance data
* ---------------------------------------

* mergent issues data
use "$raw/mergent/mergent_combined_issue.dta", clear
rename COMPLETE_CUSIP cusip
order cusip
drop if missing(cusip)
keep cusip OFFERING_AMT OFFERING_DATE OFFERING_PRICE OFFERING_YIELD DELIVERY_DATE EFFECTIVE_DATE AMOUNT_OUTSTANDING AMT_OFFERED TOTAL_UNITS_OFFERED PRINCIPAL_AMT_PER_UNIT INDUSTRY_GROUP INDUSTRY_CODE SIC_CODE
foreach var of varlist * {
    local try = lower("`var'")
    cap rename `var' `try'
}
mmerge cusip using "$raw/cmns/gcap_security_master_cusip", unmatched(m)
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m) ukeep(iq_offer_date)
drop _merge
replace offering_date = issuance_date if missing(offering_date)
replace offering_date = delivery_date if missing(offering_date)
replace offering_date = iq_offer_date if missing(offering_date)
gen issuance_year = year(offering_date)
drop iq_offer_date
save "$tmp/mergent_issues_cleaned", replace

* dealogic issues data
use "$tmp/dealogic_dcm_issuance_complete.dta", clear
keep cusip value _pricingdate yieldtomaturityannualpercent
gen issuance_year = year(_pricingdate)
drop _pricingdate
rename value offering_amt
rename yieldtomaturityannualpercent offering_yield
drop if missing(cusip)
collapse (firstnm) offering_amt issuance_year offering_yield, by(cusip)
tempfile dlg_offering
save `dlg_offering', replace

* merge the offering data
use `dlg_offering', clear
mmerge cusip using "$tmp/mergent_issues_cleaned", umatch(cusip) uname(m_) ukeep(issuance_year offering_amt offering_yield)
replace m_offering_amt = m_offering_amt / 1e3
replace offering_amt = m_offering_amt if missing(offering_amt)
replace offering_yield = m_offering_yield if missing(offering_yield)
replace issuance_year = m_issuance_year if missing(issuance_year)
drop _merge m*
drop if missing(offering_amt) & missing(offering_yield)
drop if missing(issuance_year)
replace offering_amt = . if offering_amt > 50000 // drop above 50B
save "$tmp/offering_data_merged", replace

* collapse to firm level
use "$tmp/offering_data_merged", clear
mmerge cusip using "$tmp/cusip_to_up_firm_id", unmatched(m)
drop if missing(offering_amt)
collapse (sum) offering_amt, by(firm_id issuance_year)
drop if missing(firm_id)
rename offering_amt issuance_amount
save "$tmp/yearly_bond_issuance_up", replace

* aggregate insurer shares to the firm level
use "$tmp/holdings_mf_ins_issuance_trace_universe_refined", clear
keep if date_q == tq(2007q1)
mmerge cusip using "$tmp/cusip_to_up_firm_id", unmatched(m)
assert _merge == 3
collapse (mean) share_ins_issuance [aw = value_outstanding], by(firm_id)
drop if missing(share_ins_issuance)
save "$tmp/up_ownership", replace

* issuer-level ratings
use "$tmp/issue_ratings_merged_sp_moodys_y", clear
qui mmerge cusip using "$tmp/cusip_to_up_firm_id", unmatched(m)
drop _merge
gen count = 1
qui gen ig = 1 if inlist(rating_cat, "AAA", "AA", "A", "BBB")
qui replace ig = 0 if missing(ig)
gcollapse (sum) count, by(firm_id ig rating_cat rating_cat_pm)
gsort firm_id ig -count
by firm_id ig: keep if _n == 1
gsort firm_id -ig
by firm_id: keep if _n == 1
keep firm_id rating_cat rating_cat_pm
rename rating_cat issuer_rating_cat
rename rating_cat_pm issuer_rating_cat_pm
save "$tmp/issuer_rating_cat_up", replace

* yearly firm issuance indicators
use "$tmp/yearly_bond_issuance_up", clear
gsort firm_id issuance_year
drop if missing(issuance_year)
by firm_id: egen min_year = min(issuance_year)
fillin firm_id issuance_year

gen issuance_indicator = 1 if ~missing(issuance_amount)
replace issuance_indicator = 0 if missing(issuance_amount)
replace issuance_amount = 0 if missing(issuance_amount)

by firm_id: egen _min_year = max(min_year)
replace min_year = _min_year if missing(min_year)
assert ~missing(min_year)
drop if issuance_year < min_year
drop _min_year min_year _fillin
save "$tmp/issuance_indicators_up", replace

* average offering yields: yearly
use "$tmp/offering_data_merged", clear
mmerge cusip using "$tmp/cusip_to_up_firm_id", unmatched(m)
drop if missing(offering_yield) | missing(offering_amt)
gcollapse (mean) offering_yield [aw = offering_amt], by(firm_id issuance_year)
drop if missing(firm_id)
drop if missing(offering_yield)
save "$tmp/firm_offer_yields", replace

* ---------------------------------------
* Fundamentals panel
* ---------------------------------------

* year of establishment
use "$tmp/compustat_out_ann", clear
gsort gvkey fyear
by gvkey: keep if _n == 1
keep gvkey fyear
rename fyear establishment_year
save "$tmp/compustat_na_ann_estyear", replace

* compustat north america
use "$tmp/compustat_out_ann", clear
keep if fyear >= 2000
keep gvkey id fyear fyr cusip conm capx capx_at xrd_at xrdgr aqc_at at ni xrd ppegt ///
    ppent revt naics sic aqc sppe ivch siv dltis emp conm dt ///
    ch rect oibdp opiti ni xintd xinst tie ebit ebitda mkvalt ///
    csho pnrsho prsho bkvlps prcc_c prcc_f dltt dd1
mmerge gvkey using "$tmp/compustat_na_ann_estyear", unmatched(m)
drop _merge
save "$tmp/compustat_na_essentials", replace

* gvkey to firm id
use "$tmp/compustat_na_essentials", clear
keep gvkey cusip conm
drop if missing(cusip)
collapse (firstnm) conm, by(gvkey cusip)
duplicates drop
mmerge cusip using "$tmp/cusip8_to_up_firm_id", unmatched(m)

gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m) ukeep(cgs_domicile issuer_name)
replace firm_id = issuer_number if missing(firm_id)
replace firm_name = issuer_name if missing(firm_name)
replace firm_domicile = cgs_domicile if missing(firm_domicile)

drop _merge cusip
rename firm_name cgs_name
assert ~missing(firm_id)

* make domicile unique
gsort firm_id
encode firm_domicile, gen(_firm_domicile)
by firm_id: egen __firm_domicile = max(_firm_domicile)
replace _firm_domicile = __firm_domicile
drop firm_domicile
decode _firm_domicile, gen(firm_domicile)
drop _*

gsort gvkey
save "$tmp/gvkey_to_up_id_pre", replace

* make conm unique
use "$tmp/gvkey_to_up_id_pre", clear
drop if missing(conm)
gsort firm_id conm
by firm_id conm: gen N = _N
gsort firm_id -N
by firm_id: keep if _n == 1
keep firm_id conm
save "$tmp/gvkey_to_up_id_conm", replace

* make cgs_name unique
use "$tmp/gvkey_to_up_id_pre", clear
drop if missing(cgs_name)
gsort firm_id cgs_name
by firm_id cgs_name: gen N = _N
gsort firm_id -N
by firm_id: keep if _n == 1
keep firm_id cgs_name
save "$tmp/gvkey_to_up_id_cgs_name", replace

* add in the names
use "$tmp/gvkey_to_up_id_pre", clear
mmerge firm_id using "$tmp/gvkey_to_up_id_conm", uname(u_)
assert _merge == 3
drop _merge
replace conm = u_conm
assert ~missing(conm)

mmerge firm_id using "$tmp/gvkey_to_up_id_cgs_name", uname(u_)
drop _merge
assert ~missing(u_cgs_name) if ~missing(cgs_name)
replace cgs_name = u_cgs_name
drop u_*

* save the mapping
gsort gvkey
save "$tmp/gvkey_to_up_id", replace

* collapse at firm id level: stocks
use "$tmp/compustat_na_essentials", clear
mmerge gvkey using "$tmp/gvkey_to_up_id", unmatched(m)
assert _merge == 3 & ~missing(firm_id)
drop _merge
collapse (sum) aqc at capx ivch ppegt ppent revt siv sppe xrd xrdgr dltis emp dt ch rect oibdp opiti ni xintd xinst tie ///
        ebit ebitda mkvalt csho pnrsho prsho dltt dd1 ///
    (min) establishment_year (firstnm) conm cgs_name, by(firm_id firm_domicile fyear)
save "$tmp/compustat_up_stocks", replace

* collapse at firm id level: ratios
use "$tmp/compustat_na_essentials", clear
mmerge gvkey using "$tmp/gvkey_to_up_id", unmatched(m)
assert _merge == 3 & ~missing(firm_id)
drop _merge
collapse (mean) capx_at xrd_at aqc_at bkvlps prcc_c prcc_f (firstnm) conm cgs_name [aw = at], by(firm_id firm_domicile fyear)
save "$tmp/compustat_up_ratios", replace

* industry, firm-level
use "$tmp/compustat_na_essentials", clear
keep gvkey at fyear
mmerge gvkey using "$tmp/gvkey_to_up_id", unmatched(m)
assert _merge == 3 & ~missing(firm_id)
drop _merge
mmerge firm_id using "$tmp/industry_master_naics", unmatched(m) umatch(cusip6)
gcollapse (sum) at, by(firm_id fyear naics2)
gcollapse (max) at, by(firm_id naics2)
gsort firm_id -at
by firm_id: keep if _n == 1
drop at
save "$tmp/compustat_up_industry", replace

* firm names
use "$tmp/compustat_na_essentials", clear
mmerge gvkey using "$tmp/gvkey_to_up_id", unmatched(m)
assert _merge == 3 & ~missing(firm_id)
collapse (firstnm) conm, by(firm_id)
save "$tmp/up_firm_names", replace

* collapse at firm id level: append
use "$tmp/compustat_up_stocks", clear
mmerge firm_id fyear using "$tmp/compustat_up_ratios", update
drop _merge

* panel structure
encode firm_id, gen(_firm_id)
xtset _firm_id fyear
gsort _firm_id fyear

* rates
gen employment_growth = (emp - l.emp) / (0.5 * (emp + l.emp))
gen capx_at_bg = capx / l.at
gen capx_ppent_bg = capx / l.ppent
gen capx_xrd_at_bg = (capx + xrd) / l.at

* add names
mmerge firm_id using "$tmp/up_firm_names", unmatched(m)
drop _merge
save "$tmp/compustat_up_essentials_y", replace

* yearly frame for firm-level regressions
use "$tmp/compustat_up_essentials_y", clear
rename fyear year
gen xin = xintd + xinst                  // interest expense
gen dt_at = dt / at                      // debt by assets
gen dltt_at = dltt / at
gen ch_rect_at = (ch + rect) / at        // cash and receivables by assets
gen opiti_xin = opiti / xin              // operating income by interest expense
gen ni_at = ni / at                      // net income by assets
gen oibdp_at = oibdp / at                // operating income before depreciation by assets
gen log_at = log(at)
gen ebit_at = ebit / at
gen dt_ebit = dt / ebit
gen bookval = bkvlps * (csho + pnrsho + prsho)
gen log_market_to_book = log(mkvalt / bookval)
gen capx_aqc_at = capx_at + aqc_at
keep firm_id year capx_at aqc_at capx_aqc_at dt_at ch_rect_at opiti_xin ni_at oibdp_at log_at ebit_at dt_ebit log_market_to_book dltt_at

mmerge firm_id using "$tmp/up_ownership", unmatched(m)
keep if _merge == 3
mmerge firm_id using "$tmp/compustat_up_industry", unmatched(m)
drop _merge
mmerge firm_id using "$tmp/issuer_rating_cat_up", unmatched(m)
keep if inrange(year, 2004, 2011)
encode firm_id, gen(_firm_id)
gsort _firm_id year
xtset _firm_id year
encode issuer_rating_cat, gen(_issuer_rating_cat)
encode issuer_rating_cat_pm, gen(_issuer_rating_cat_pm)

foreach var of varlist log_at ch_rect_at ebit_at dt_ebit log_market_to_book {
    gen l1_`var' = l.`var'
}
gen post = 0
replace post = 1 if year >= 2008
save "$tmp/up_investment_summary_y", replace

* ---------------------------------------
* Maturing bond shares
* ---------------------------------------

* catalog of firms
use "$tmp/up_investment_summary_y", clear
keep firm_id
bys firm_id: keep if _n == 1
save "$tmp/up_investment_catalog", replace

* get outstanding bonds
use cusip class_code1 class_code2 issuance_date maturity_date using "$raw/cmns/gcap_security_master_cusip.dta" if class_code1 == "B", clear
assert class_code1 == "B"
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation.dta", unmatched(m)
keep if _merge == 3
rename cusip6_up_bg firm_id
mmerge firm_id using "$tmp/up_investment_catalog", unmatched(m)
keep if _merge == 3
drop _merge *source*
rename issuance_date sm_issuance_date
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m) ukeep(iq_offer_date)
rename iq_offer_date issuance_date
replace issuance_date = sm_issuance_date if missing(issuance_date)
drop sm_issuance_date

* construct shares of debt due
drop if missing(maturity_date) | missing(issuance_date)
gen year = 2007
mmerge cusip year using "$tmp/amounts_outstanding_y", unmatched(m)
keep if _merge == 3

gen maturing = 0
replace maturing = 1 if maturity_date >= td(01jan2008) & maturity_date <= td(31dec2009)
gcollapse (sum) value_outstanding, by(firm_id maturing)
fillin firm_id maturing
replace value_outstanding = 0 if missing(value_outstanding)
drop _fillin
by firm_id: egen totValue = total(value_outstanding)
gen share_maturing = value_outstanding / totValue

keep if maturing == 1
drop value_outstanding* totValue*
drop maturing
gsort firm_id
by firm_id: gen N = _N
assert N == 1
drop N
save "$tmp/up_shares_maturing", replace
